﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using DAL.Entities;

namespace DAL.Entities_DAL
{
    public class TaikhoanDAL: BaseDAL
    {
        public DataTable Select()
        {
            //mo csdl table
            var sql = @"SELECT * FROM TAI_KHOAN";
            return ExecuteQuery(sql);
        }

        public object GetByKey(object key)
        {
            //dang nhap xong hien len lable quyen, ten
            var sql = @"select a.Ten_dang_nhap, mat_khau, Quyen_truy_cap, HOTEN_NV , ma_nv
                        from TAI_KHOAN a join NHAN_VIEN b on a.Ten_dang_nhap = b.Ten_dang_nhap 
                        where a.TEN_DANG_NHAP = '{0}' ";
            sql = String.Format(sql, key);
            var tbl = ExecuteQuery(sql);
            
            if (tbl.Rows.Count > 0)
            {
                var usr = new Entities.Taikhoan();
                usr.TEN_DANG_NHAP = tbl.Rows[0]["Ten_dang_nhap"] + "";
                usr.MAT_KHAU = tbl.Rows[0]["Mat_khau"] + "";
                usr.QUYEN_TRUY_CAP = Convert.ToInt32(tbl.Rows[0]["Quyen_truy_cap"] + "");
                usr.hoten = tbl.Rows[0]["Hoten_NV"] + "";
                usr.manv = tbl.Rows[0]["ma_nv"] + "";
                return usr;
            }
            else return null;
        }

        //xoa tai khoan
        public bool Delete(string id)
        {
            var sql = @"DELETE FROM TAI_KHOAN WHERE TEN_DANG_NHAP = '{0}'";
            sql = string.Format(sql, id);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        //them tai khoan
        public bool Insert(object obj)
        {
            var o = (Taikhoan)obj;
            string sql = @"INSERT INTO TAI_KHOAN(TEN_DANG_NHAP, MAT_KHAU, QUYEN_TRUY_CAP) VALUES ( '{0}', N'{1}', N'{2}' ) ";
            sql = string.Format(sql, o.TEN_DANG_NHAP, o.MAT_KHAU, o.QUYEN_TRUY_CAP);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        //sua tai khoan
        public bool Update(object obj)
        {
            var o = (Taikhoan)obj;
            string sql = @"set dateformat dmy; UPDATE TAI_KHOAN SET MAT_KHAU= '{1}', QUYEN_TRUY_CAP = N'{2}' WHERE TEN_DANG_NHAP='{0}'";
            sql = string.Format(sql, o.TEN_DANG_NHAP, o.MAT_KHAU, o.QUYEN_TRUY_CAP);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }
        //lay matkhau tu ten dang nhap
        public string changepass(string id)
        {
            string sql = @"select mat_khau from tai_khoan where ten_dang_nhap = '{0}' ";
            sql = string.Format(sql, id);
            DataTable dt = ExecuteQuery(sql);
            return dt.Rows.Count > 0 ? dt.Rows[0]["mat_khau"].ToString() : "null";
        }
        //doi mat khau ben form doi mat khau trueyn qua lu vao
        public bool doipass(string id, string moi)
        {
            string sql = @"update tai_khoan set mat_khau='{0}' where ten_dang_nhap = '{1}' ";
            sql = string.Format(sql, id, moi);
            DataTable dt = ExecuteQuery(sql);
            return dt.Rows.Count > 0 ? true: false;
        }

       
    }
}
